﻿IF OBJECT_ID ('CHO_TASK_BATCH') IS NOT NULL
	DROP TABLE CHO_TASK_BATCH
GO

CREATE TABLE CHO_TASK_BATCH
	(
	BATCH_ID   	  INT NOT NULL,
	TASK_ID	  	  INT NOT NULL,
	SCHEDULE_ID	  INT NOT NULL,
	TASK_STATUS	  INT NOT NULL,
	CMD_PARAMETERS NVARCHAR(2500),
	MACHINE_NAME	VARCHAR(50),
	LOG_MSG		  NVARCHAR(2500),
	START_TIME	  DATETIME NOT NULL,
	END_TIME      DATETIME,
	DURATION      NVARCHAR(2500),
	NEXT_RUN_TIME DATETIME NULL,
	EXIT_CODE	  INT NOT NULL DEFAULT(0),
	RETRY_COUNT	  INT NOT NULL DEFAULT(0),
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_BATCH PRIMARY KEY (BATCH_ID, TASK_ID, SCHEDULE_ID)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_TASK_BATCH_AUDIT') IS NOT NULL
	DROP TABLE CHO_TASK_BATCH_AUDIT
GO

CREATE TABLE CHO_TASK_BATCH_AUDIT
	(
	BATCH_ID   	  INT NOT NULL,
	TASK_ID	  	  INT NOT NULL,
	SCHEDULE_ID	  INT NOT NULL,
	TASK_STATUS	  INT NOT NULL,
	CMD_PARAMETERS NVARCHAR(2500),
	MACHINE_NAME	VARCHAR(50),
	LOG_MSG		  NVARCHAR(2500),
	START_TIME	  DATETIME NOT NULL,
	END_TIME      DATETIME,
	DURATION      NVARCHAR(2500),
	NEXT_RUN_TIME DATETIME NULL,
	EXIT_CODE	  INT NOT NULL DEFAULT(0),
	RETRY_COUNT	  INT NOT NULL DEFAULT(0),
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_BATCH_AUDIT PRIMARY KEY (BATCH_ID, TASK_ID, SCHEDULE_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK_BATCH') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK_BATCH
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK_BATCH
		@BATCH_ID INT,
		@TASK_NAME NVARCHAR(50),
		@SCHEDULE_NAME NVARCHAR(50),
		@TASK_STATUS INT,
		@CMD_PARAMETERS NVARCHAR(2500),
		@MACHINE_NAME VARCHAR(50),
		@LOG_MSG NVARCHAR(2500),
		@START_TIME	DATETIME,
		@END_TIME DATETIME,
		@DURATION NVARCHAR(2500),
		@NEXT_RUN_TIME DATETIME,
		@EXIT_CODE INT,
		@RETRY_COUNT INT,
		@MODIFIED_BY NVARCHAR(100)
		AS

	SET NOCOUNT ON

	DECLARE @MODIFIED_DATE DATETIME
	DECLARE @ERR_MESSAGE NVARCHAR(255)
	DECLARE @TASK_ID INT
	DECLARE @SCHEDULE_ID INT

	SELECT @TASK_ID = TASK_ID FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME AND DELETED = 0

	IF @BATCH_ID IS NULL
	BEGIN
		SET @ERR_MESSAGE = @TASK_NAME + ' batch id is null.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	

	IF @TASK_ID IS NULL
	BEGIN
		SET @ERR_MESSAGE = @TASK_NAME + ' task not exists/deleted.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	
	
	SELECT @SCHEDULE_ID = SCHEDULE_ID FROM CHO_TASK_SCHEDULES WHERE TASK_ID = @TASK_ID AND SCHEDULE_NAME = @SCHEDULE_NAME
		
	IF @SCHEDULE_ID IS NULL
	BEGIN
		SET @ERR_MESSAGE = @TASK_NAME + ' Schedule id is null.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	

	SELECT @MODIFIED_DATE = GETDATE()
	
	IF NOT EXISTS(SELECT 1 FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID AND BATCH_ID = @BATCH_ID AND SCHEDULE_ID = @SCHEDULE_ID)
	BEGIN
		IF (@START_TIME is NULL)
			SELECT @START_TIME = GETDATE()
		IF (@EXIT_CODE is NULL)
			SELECT @EXIT_CODE = 0
		IF (@RETRY_COUNT is NULL)
			SELECT @RETRY_COUNT = 0
		ELSE IF (@NEXT_RUN_TIME = '1753-01-01')
			SELECT @NEXT_RUN_TIME = NULL

		DELETE FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID AND SCHEDULE_ID = @SCHEDULE_ID

		INSERT CHO_TASK_BATCH (BATCH_ID, TASK_ID, SCHEDULE_ID, TASK_STATUS, CMD_PARAMETERS, LOG_MSG, START_TIME, END_TIME, DURATION, NEXT_RUN_TIME, EXIT_CODE, 
			RETRY_COUNT, MODIFIED_BY, MODIFIED_DATE)
		VALUES (@BATCH_ID, @TASK_ID, @SCHEDULE_ID, @TASK_STATUS, @CMD_PARAMETERS, @LOG_MSG, @START_TIME, @END_TIME, @DURATION, @NEXT_RUN_TIME, @EXIT_CODE, 
			@RETRY_COUNT, @MODIFIED_BY, @MODIFIED_DATE)
	END
	ELSE
	BEGIN
		IF (@TASK_STATUS is NULL)
			SELECT @TASK_STATUS = TASK_STATUS FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@CMD_PARAMETERS is NULL)
			SELECT @CMD_PARAMETERS = CMD_PARAMETERS FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@MACHINE_NAME is NULL)
			SELECT @MACHINE_NAME = MACHINE_NAME FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@LOG_MSG is NULL)
			SELECT @LOG_MSG = LOG_MSG FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@START_TIME is NULL)
			SELECT @START_TIME = START_TIME FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@END_TIME is NULL)
			SELECT @END_TIME = END_TIME FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@DURATION is NULL)
			SELECT @DURATION = DURATION FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@NEXT_RUN_TIME is NULL)
			SELECT @NEXT_RUN_TIME = NEXT_RUN_TIME FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		ELSE IF (@NEXT_RUN_TIME = '1753-01-01')
			SELECT @NEXT_RUN_TIME = NULL
		IF (@RETRY_COUNT is NULL)
			SELECT @RETRY_COUNT = RETRY_COUNT FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID
		IF (@EXIT_CODE is NULL)
			SELECT @EXIT_CODE = EXIT_CODE FROM CHO_TASK_BATCH WHERE TASK_ID = @TASK_ID

		UPDATE CHO_TASK_BATCH
			SET 
				TASK_STATUS = @TASK_STATUS,
				CMD_PARAMETERS = @CMD_PARAMETERS,
				MACHINE_NAME = @MACHINE_NAME,
				LOG_MSG = @LOG_MSG,
				START_TIME = @START_TIME,
				END_TIME = @END_TIME,
				DURATION = @DURATION,
				NEXT_RUN_TIME = @NEXT_RUN_TIME,
				EXIT_CODE = @EXIT_CODE,
				RETRY_COUNT = @RETRY_COUNT,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @MODIFIED_DATE
			WHERE
				TASK_ID = @TASK_ID
				AND BATCH_ID = @BATCH_ID
				AND SCHEDULE_ID = @SCHEDULE_ID						
		
		INSERT INTO CHO_TASK_BATCH_AUDIT 
			SELECT * FROM CHO_TASK_BATCH
			WHERE TASK_ID = @TASK_ID AND BATCH_ID = @BATCH_ID AND SCHEDULE_ID = @SCHEDULE_ID
	END

GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SET_TASK_STATUS') IS NOT NULL
	DROP PROC CHO_SET_TASK_STATUS
GO

CREATE PROCEDURE CHO_SET_TASK_STATUS
		@BATCH_ID INT,
		@TASK_NAME NVARCHAR(50),
		@SCHEDULE_NAME NVARCHAR(50),
		@TASK_STATUS INT,
		@LOG_MSG NVARCHAR(2500),
		@NEXT_RUN_TIME DateTime,
		@MODIFIED_BY NVARCHAR(100)
		AS
BEGIN

	SET NOCOUNT ON

	DECLARE @DELETED BIT
	DECLARE @TASK_ID INT
	DECLARE @SCHEDULE_ID INT
	DECLARE @ERR_MESSAGE NVARCHAR(255)

	SELECT @TASK_ID = TASK_ID, @DELETED = DELETED FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME

	IF (@TASK_ID IS NULL)
	BEGIN
		SET @ERR_MESSAGE = @TASK_NAME + ' task not exists.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	

	IF @DELETED = 1
	BEGIN
		RETURN
	END	

	IF (@MODIFIED_BY IS NULL)
		SELECT @MODIFIED_BY = 'SYSTEM'

	IF NOT EXISTS(SELECT 1 FROM CHO_TASK_STATUS WHERE TASK_STATUS_ID = @TASK_STATUS)
		BEGIN
		SET @ERR_MESSAGE = 'Invalid ' + CAST(@TASK_STATUS as NVARCHAR(25)) + ' task status passed.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	

	EXEC CHO_ADD_OR_UPDATE_TASK_BATCH
		@BATCH_ID,
		@TASK_NAME,
		@SCHEDULE_NAME,
		@TASK_STATUS,
		NULL,
		@LOG_MSG,
		NULL,
		NULL,
		NULL,
		@NEXT_RUN_TIME,
		NULL,
		NULL,
		@MODIFIED_BY
END
GO


